package lg.minecraft.plugin.pokedex.storage;

import com.cobblemon.mod.common.pokemon.Species;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import lg.minecraft.plugin.pokedex.Generations;
import lg.minecraft.plugin.pokedex.LGPokedex;
import org.jetbrains.annotations.NotNull;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

/**
 * @author langle__
 * @version 1.0
 */
public class MYSQLStorage extends DataStorage {

    private static final String CREATE_TABLE_PLAYERS_SQL = """
            CREATE TABLE IF NOT EXISTS pokedex_players (
                uuid CHAR(36) PRIMARY KEY,
                gen1 BOOLEAN DEFAULT false,
                gen2 BOOLEAN DEFAULT false,
                gen3 BOOLEAN DEFAULT false,
                gen4 BOOLEAN DEFAULT false,
                gen5 BOOLEAN DEFAULT false,
                gen6 BOOLEAN DEFAULT false,
                gen7 BOOLEAN DEFAULT false,
                gen8 BOOLEAN DEFAULT false,
                gen9 BOOLEAN DEFAULT false,
                all_gen BOOLEAN DEFAULT false
            );
            """;

    private static final String CREATE_TABLE_RECEIVED_SPECIES_SQL = """
            CREATE TABLE IF NOT EXISTS received_species (
                uuid CHAR(36),
                species_name VARCHAR(255),
                PRIMARY KEY (uuid, species_name),
                FOREIGN KEY (uuid) REFERENCES pokedex_players(uuid) ON DELETE CASCADE
            );
            """;

    private static final String UPSERT_GENERATION_REWARD_CLAIMED_SQL = "INSERT INTO pokedex_players (uuid, %s) VALUES (?, ?) ON DUPLICATE KEY UPDATE %s = VALUES(%s)";
    private static final String CHECK_GENERATION_REWARD_CLAIMED_SQL = "SELECT %s FROM pokedex_players WHERE uuid = ?";
    private static final String MARK_SPECIES_AS_RECEIVED_SQL = "INSERT INTO received_species (uuid, species_name) VALUES (?, ?) ON DUPLICATE KEY UPDATE species_name = species_name";
    private static final String UNMARK_SPECIES_AS_RECEIVED_SQL = "DELETE FROM received_species WHERE uuid = ? AND species_name = ?";
    private static final String CHECK_SPECIES_RECEIVED_SQL = "SELECT COUNT(*) FROM received_species WHERE uuid = ? AND species_name = ?";
    private static final String GET_CLAIMED_REWARD_SPECIES_SQL = "SELECT species_name FROM received_species WHERE uuid = ?";
    private static final String ENSURE_PLAYER_EXISTS_SQL = "INSERT IGNORE INTO pokedex_players (uuid) VALUES (?)";

    private final String url;
    private final String user;
    private final String password;

    private HikariDataSource dataSource;

    public MYSQLStorage(String url, String user, String password) {
        this.url = url;
        this.user = user;
        this.password = password;
        initialize();
    }

    private void initialize() {
        try {
            HikariConfig config = new HikariConfig();
            config.setJdbcUrl(url);
            config.setUsername(user);
            config.setPassword(password);

            config.setMaximumPoolSize(10);
            config.setMinimumIdle(2);
            config.setConnectionTimeout(30000);
            config.setIdleTimeout(600000);
            config.setMaxLifetime(1800000);

            this.dataSource = new HikariDataSource(config);

            try (
                    Connection connection = dataSource.getConnection();
                    Statement statement = connection.createStatement()
            ) {
                statement.executeUpdate(CREATE_TABLE_PLAYERS_SQL);
                statement.executeUpdate(CREATE_TABLE_RECEIVED_SPECIES_SQL);
            }
        } catch (SQLException e) {
            LGPokedex.getInstance().getLogger().severe(e.toString());
        }
    }

    @Override
    public void setGenerationRewardClaimed(@NotNull UUID uuid, @NotNull Generations gen, boolean claimed) {
        String columnName = gen.name.toLowerCase();
        String sql = String.format(UPSERT_GENERATION_REWARD_CLAIMED_SQL, columnName, columnName, columnName);
        try (
                Connection connection = dataSource.getConnection();
                var statement = connection.prepareStatement(sql)
        ) {
            statement.setString(1, uuid.toString());
            statement.setBoolean(2, claimed);
            statement.executeUpdate();
        } catch (SQLException e) {
            LGPokedex.getInstance().getLogger().severe("Failed to upsert generation reward claim status: " + e.getMessage());
        }
    }

    @Override
    public boolean isGenerationRewardClaimed(@NotNull UUID uuid, @NotNull Generations gen) {
        String sql = String.format(CHECK_GENERATION_REWARD_CLAIMED_SQL, gen.name.toLowerCase());
        try (
                Connection connection = dataSource.getConnection();
                var statement = connection.prepareStatement(sql)
        ) {
            statement.setString(1, uuid.toString());
            var resultSet = statement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getBoolean(gen.name.toLowerCase());
            }
        } catch (SQLException e) {
            LGPokedex.getInstance().getLogger().severe("Failed to check generation reward claim status: " + e.getMessage());
        }
        return false;
    }

    @Override
    public void markSpeciesAsReceived(@NotNull UUID uuid, @NotNull Species species) {
        ensurePlayerExists(uuid);
        try (
                Connection connection = dataSource.getConnection();
                var statement = connection.prepareStatement(MARK_SPECIES_AS_RECEIVED_SQL)
        ) {
            statement.setString(1, uuid.toString());
            statement.setString(2, species.resourceIdentifier.toString());
            statement.executeUpdate();
        } catch (SQLException e) {
            LGPokedex.getInstance().getLogger().severe("Failed to mark species as received: " + e.getMessage());
        }
    }

    private void ensurePlayerExists(@NotNull UUID uuid) {
        try (
                Connection connection = dataSource.getConnection();
                var statement = connection.prepareStatement(ENSURE_PLAYER_EXISTS_SQL)
        ) {
            statement.setString(1, uuid.toString());
            statement.executeUpdate();
        } catch (SQLException e) {
            LGPokedex.getInstance().getLogger().severe("Failed to ensure player exists: " + e.getMessage());
        }
    }

    @Override
    public void unmarkSpeciesAsReceived(@NotNull UUID uuid, @NotNull Species species) {
        try (
                Connection connection = dataSource.getConnection();
                var statement = connection.prepareStatement(UNMARK_SPECIES_AS_RECEIVED_SQL)
        ) {
            statement.setString(1, uuid.toString());
            statement.setString(2, species.resourceIdentifier.toString());
            statement.executeUpdate();
        } catch (SQLException e) {
            LGPokedex.getInstance().getLogger().severe("Failed to unmark species as received: " + e.getMessage());
        }
    }

    @Override
    public boolean hasSpeciesBeenReceived(@NotNull UUID uuid, @NotNull Species species) {
        try (
                Connection connection = dataSource.getConnection();
                var statement = connection.prepareStatement(CHECK_SPECIES_RECEIVED_SQL)
        ) {
            statement.setString(1, uuid.toString());
            statement.setString(2, species.resourceIdentifier.toString());
            var resultSet = statement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1) > 0;
            }
        } catch (SQLException e) {
            LGPokedex.getInstance().getLogger().severe("Failed to check if species has been received: " + e.getMessage());
        }
        return false;
    }

    @Override
    public List<String> getClaimedRewardSpecies(@NotNull UUID uuid) {
        var result = new ArrayList<String>();
        try (
                Connection connection = dataSource.getConnection();
                var statement = connection.prepareStatement(GET_CLAIMED_REWARD_SPECIES_SQL)
        ) {
            statement.setString(1, uuid.toString());
            var resultSet = statement.executeQuery();
            while (resultSet.next()) {
                result.add(resultSet.getString("species_name"));
            }
        } catch (SQLException e) {
            LGPokedex.getInstance().getLogger().severe("Failed to retrieve claimed reward species: " + e.getMessage());
        }
        return result;
    }

    public void close() {
        try {
            if (dataSource != null && !dataSource.isClosed()) {
                dataSource.close();
            }
        } catch (Exception e) {
            LGPokedex.getInstance().getLogger().severe(e.toString());
        }
    }

}